package com.wanda.util;


import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ttsx.platform.tool.excel.util.ExcelSheetCopy;
import com.wanda.model.Parameters;

public abstract class  ExcelBase {
	protected HSSFCellStyle style;
	protected HSSFSheet sheet;
	protected HSSFWorkbook workbook;
	private Parameters params;  //在自己电脑model中有这个类不
	private DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	protected static final int DATA_NUM = 60000;
	
	public void generateFormatExcel(Object obj , Parameters params,HttpServletResponse resp) throws Exception{
		this.params = params;
		InputStream 
		input = null;
		try{
			input = ExcelBase.class.getClassLoader().getResourceAsStream(params.getTemplatePath());
			this.workbook = new HSSFWorkbook(input);
			this.sheet = workbook.getSheet(params.getSheetName());
			this.style = this.cellStyle(this.workbook);
			this.setHeader();
			this.setTimingFormat();
			this.setBusiData(obj);
			this.fileDownLoad(this.workbook,params.getName(),resp);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(null!=input){
				try{
					input.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
	}
	public void generateFormatExcelNoTime(Object obj , Parameters params,HttpServletResponse resp) throws Exception{
		this.params = params;
		InputStream input = null;
		try{
			input = ExcelBase.class.getClassLoader().getResourceAsStream(params.getTemplatePath());
			this.workbook = new HSSFWorkbook(input);
			this.sheet = workbook.getSheet(params.getSheetName());
			this.style = this.cellStyle(this.workbook);
			this.setHeader();
			this.setBusiData(obj);
			this.fileDownLoad(this.workbook,params.getName(),resp);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(null!=input){
				try{
					input.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
	}
	public static void fileDownLoad(HSSFWorkbook workbook, String name,
			HttpServletResponse resp) throws Exception {
		OutputStream out = null;
		try{
			resp.setHeader("Accept-Ranges","bytes");
			name = URLEncoder.encode(name+".xls","UTF-8");
			resp.setHeader("Content-Disposition", "attachment;filename=" + name);
			resp.setHeader("Content-Type", "application/vnd.ms-excel");
			out = new BufferedOutputStream(resp.getOutputStream());
			System.out.println(name);
			workbook.write(out);
			out.flush();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(null != out)out.close();
		}
		
	}
	protected  void setBusiData(Object obj) {
		List<?> result =(List<?>) obj;
		int size = result.size();
		if(size<DATA_NUM){
			this.renderBusiData(result);
		}else{
			int sheetNum = getUpperIntValue(size,DATA_NUM);
			int renderDataIndex = DATA_NUM;
			ExcelSheetCopy excelSheetCopy = null;
			for(int i=1 ; i<sheetNum;i++){
				excelSheetCopy = new ExcelSheetCopy();
				String destSheetName = "sheet" + i;
				excelSheetCopy.copySheet("sheet", destSheetName, this.workbook);
				this.sheet = this.workbook.getSheet(destSheetName);
				this.setHeader();
				this.setTimingFormat();
				List<?> renderList = result.subList(0, renderDataIndex);
				this.renderBusiData(renderList);
				renderList.clear();
				if(!(result.isEmpty())&&result.size()<=DATA_NUM)renderDataIndex = result.size();
			}
		}
	}


	public abstract void renderBusiData(List<?> renderList);


	private int getUpperIntValue(int a, int b) {
		return  (((double) a / (double) b) > (a / b) ? a / b + 1 : a / b);
	}


	private void setTimingFormat() {
		HSSFRow timingRow = sheet.getRow(1);
		HSSFCell timingCell = timingRow.getCell(params.getExportTimeCellNum());
		HSSFCell statisticCell = timingRow.getCell(0);
		statisticCell.setCellValue(params.getStatictisDate());
		timingCell.setCellValue("导出时间:" + format.format(new Date()));
	}


	private void setHeader() {
		HSSFRow headerRow = sheet.getRow(0);
		HSSFCell headerCell = headerRow.getCell(0);
		headerCell.setCellValue(params.getName());
	}
	private HSSFCellStyle cellStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style = this.workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		return style;
	}
	
	protected String getValue(Object object,String key){
		try{
			Field field = null;
			try{
				field = object.getClass().getDeclaredField(key);
				field.setAccessible(true);
				if(field.get(object)==null){
					return "";
				}else{
					return field.get(object).toString();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
			field = object.getClass().getSuperclass().getDeclaredField(key);
			field.setAccessible(true);
			return field.get(object).toString();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	protected void setFormula(int loopNum,int startNum,int row){
		int startLetterNum=66;
		for(int i =1;i<loopNum;i++){
			HSSFRow formulaRow = sheet.getRow(row);
			char charValue = (char) startLetterNum;
			formulaRow.getCell(i).setCellFormula("SUM(" + charValue +""+startNum +":"+charValue + "60000)");
			startLetterNum++;
		}
	}
}
